/*
 * $RCSfile: MySql.java,v $$
 * $Revision: 1.1 $
 * $Date: 2016-11-5 $
 *
 * Copyright (C) 2008 Skin, Inc. All rights reserved.
 *
 * This software is the proprietary information of Skin, Inc.
 * Use is subject to license terms.
 */
package com.skin.webcat.database.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.skin.database.Jdbc;
import com.skin.webcat.database.IndexInfo;

/**
 * <p>Title: MySql</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * @author xuesong.net
 * @version 1.0
 */
public class MySql {
    /**
     * @param host
     * @param port
     * @param database
     * @param userName
     * @param password
     * @return Connection
     * @throws SQLException
     */
    public static Connection connect(String host, String port, String database, String userName, String password) throws SQLException {
        StringBuilder url = new StringBuilder();
        url.append("jdbc:mysql://");
        url.append(host);

        if(port != null && port.trim().length() > 0) {
            url.append(":");
            url.append(port);
        }

        url.append("/");
        url.append(database);
        url.append("?user=");
        url.append(userName);
        url.append("&password=");
        url.append(password);
        url.append("&characterEncoding=utf8");
        return Jdbc.connect(url.toString(), "com.mysql.jdbc.Driver", userName, password);
    }

    /**
     * @param host
     * @param port
     * @param databaseName
     * @param userName
     * @param password
     * @throws SQLException
     */
    public static void create(String host, String port, String databaseName, String userName, String password) throws SQLException {
        String url = "jdbc:mysql://" + host;

        if(port != null) {
            url = url + ":" + port;
        }

        url = url + "?user=" + userName + "&password=" + password + "&characterEncoding=utf8";
        Connection connection = null;
        Statement statement = null;

        try {
            connection = Jdbc.connect(url, "com.mysql.jdbc.Driver", userName, password);
            statement = connection.createStatement();
            String dropDatebaseSql = "drop database if exists " + databaseName;
            String createDatabaseSql = "create database " + databaseName + " character set utf8";
            statement.execute(dropDatebaseSql);
            statement.execute(createDatabaseSql);
        }
        finally {
            if(connection != null) {
                try {
                    if(connection.isClosed() == false) {
                        connection.close();
                    }
                }
                catch(SQLException e) {
                }
            }

            if(statement != null) {
                try {
                    statement.close();
                }
                catch(SQLException e) {
                }
            }
        }
    }

    /**
     * @param host
     * @param port
     * @param databaseName
     * @param userName
     * @param password
     * @throws SQLException
     */
    public static void alterDatabaseCharset(String host, String port, String databaseName, String userName, String password) throws SQLException {
        Connection connection = null;
        Statement statement = null;

        try {
            connection = connect(host, port, databaseName, userName, password);
            statement = connection.createStatement();
            statement.execute("alter database " + databaseName + " character set utf8");
        }
        finally {
            if(connection != null) {
                try {
                    if(connection.isClosed() == false) {
                        connection.close();
                    }
                }
                catch(SQLException e) {
                }
            }

            if(statement != null) {
                try {
                    statement.close();
                }
                catch(SQLException e) {
                }
            }
        }
    }

    /**
     * @param connection
     * @param tableName
     * @param primaryKey
     * @param merge
     * @return List<TableIndex>
     * @throws SQLException
     */
    public static List<IndexInfo> getIndexInfoList(Connection connection, String tableName, boolean primaryKey, boolean merge) throws SQLException {
        List<IndexInfo> indexInfoList = new ArrayList<IndexInfo>();
        List<TableIndex> tableIndexList = getTableIndexList(connection, tableName, primaryKey, merge);

        if(tableIndexList != null) {
            for(TableIndex tableIndex : tableIndexList) {
                IndexInfo indexInfo = getIndexInfo(tableIndex);
                indexInfoList.add(indexInfo);
            }
        }
        return indexInfoList;
    }

    /**
     * @param tableIndex
     * @return IndexInfo
     */
    private static IndexInfo getIndexInfo(TableIndex tableIndex) {
        IndexInfo indexInfo = new IndexInfo();
        indexInfo.setTableName(tableIndex.getTable());
        indexInfo.setIndexName(tableIndex.getIndexName());
        indexInfo.setColumnName(tableIndex.getColumnName());
        indexInfo.setNonUnique(tableIndex.getNonUnique());
        indexInfo.setIndexType(tableIndex.getIndexType());
        return indexInfo;
    }

    /**
     * @param connection
     * @param tableName
     * @param primaryKey
     * @param merge
     * @return List<TableIndex>
     * @throws SQLException
     */
    public static List<TableIndex> getTableIndexList(Connection connection, String tableName, boolean primaryKey, boolean merge) throws SQLException {
        Statement statement = null;
        ResultSet resultSet = null;
        List<TableIndex> tableIndexList = new ArrayList<TableIndex>();

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("show index from " + tableName);

            while(resultSet.next()) {
                /**
                 * 注: mysql的ResultSetMetaData中的列名与实际的结果集中的列名不同
                 * ResultSetMetaData中的列名:
                 * TABLE_NAME, NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, COLLATION, CARDINALITY, SUB_PART, PACKED, NULLABLE, INDEX_TYPE, COMMENT
                 */
                TableIndex tableIndex = new TableIndex();
                tableIndex.setTable(resultSet.getString("TABLE"));
                tableIndex.setNonUnique(resultSet.getInt("NON_UNIQUE"));
                tableIndex.setIndexName(resultSet.getString("KEY_NAME"));
                tableIndex.setSeqInIndex(resultSet.getInt("SEQ_IN_INDEX"));
                tableIndex.setColumnName(resultSet.getString("COLUMN_NAME"));
                tableIndex.setCollation(resultSet.getString("COLLATION"));
                tableIndex.setCardinality(resultSet.getLong("CARDINALITY"));
                tableIndex.setSubPart(resultSet.getInt("SUB_PART"));
                tableIndex.setPacked(resultSet.getString("PACKED"));
                tableIndex.setNullable(resultSet.getString("NULL"));
                tableIndex.setIndexType(resultSet.getString("INDEX_TYPE"));
                tableIndex.setComment(resultSet.getString("COMMENT"));
                String indexType = tableIndex.getIndexType();

                if(indexType != null) {
                    if(indexType.equals("FULLTEXT")) {
                        tableIndex.setNonUnique(2);
                        tableIndex.setIndexType("");
                    }
                    if(indexType.equals("SPATIAL")) {
                        tableIndex.setNonUnique(3);
                        tableIndex.setIndexType("");
                    }
                }

                if(primaryKey || !"PRIMARY".equals(tableIndex.getIndexName())) {
                    tableIndexList.add(tableIndex);
                }
            }

            if(merge) {
                return merge(tableIndexList);
            }
            else {
                return tableIndexList;
            }
        }
        finally {
            close(resultSet);
            close(statement);
        }
    }
    
    /**
     * @param tableIndexList
     * @return List<TableIndex>
     */
    public static List<TableIndex> merge(List<TableIndex> tableIndexList) {
        List<TableIndex> result = new ArrayList<TableIndex>();

        if(tableIndexList != null && tableIndexList.size() > 0) {
            Map<String, TableIndex> group = new HashMap<String, TableIndex>();

            for(TableIndex tableIndex : tableIndexList) {
                String indexName = tableIndex.getIndexName();
                TableIndex model = group.get(indexName);

                if(model == null) {
                    result.add(tableIndex);
                    group.put(indexName, tableIndex);
                }
                else {
                    String columnName = model.getColumnName();
                    model.setColumnName(columnName + ", " + tableIndex.getColumnName());
                }
            }
        }
        return result;
    }

    /**
     * @param url
     * @return String
     */
    public static String getHost(String url) {
        return getHost(url, false);
    }

    /**
     * @param url
     * @return String
     */
    public static String getPort(String url) {
        String host = getHost(url, true);

        if(host != null) {
            int k = host.indexOf(":");
            
            if(k > -1) {
                return host.substring(k + 1);
            }
        }
        return null;
    }

    /**
     * @param url
     * @param port
     * @return String
     */
    public static String getHost(String url, boolean port) {
        int i = url.indexOf("jdbc:mysql://");

        if(i < 0) {
            return null;
        }

        String host = null;
        int j = url.indexOf("/", i + 13);

        if(j > -1) {
            host = url.substring(i + 13, j);
        }
        else {
            host = url.substring(i + 13);
        }

        if(port) {
            return host;
        }

        int k = host.indexOf(":");

        if(k > -1) {
            return host.substring(0, k);
        }
        else {
            return host;
        }
    }

    /**
     * @param url
     * @return String
     */
    public static String getDatabase(String url) {
        int i = url.indexOf("jdbc:mysql://");

        if(i < 0) {
            return null;
        }

        int j = url.indexOf("/", i + 13);

        if(j > -1) {
            String database = url.substring(j + 1);
            j = database.indexOf("?");

            if(j > -1) {
                database = database.substring(0, j);
            }

            j = database.indexOf("&");
            
            if(j > -1) {
                database = database.substring(0, j).trim();
            }
            return (database.length() > 0 ? database: null);
        }
        else {
            return null;
        }
    }

    /**
     * @param url
     * @return Map<String, String>
     */
    public static Map<String, String> parse(String url) {
        Map<String, String> map = new HashMap<String, String>();
        if(url == null) {
            return map;
        }

        String protocol = null;
        String address = null;
        int k = url.indexOf("://");

        if(k > -1) {
            protocol = url.substring(0, k).trim();
            address = url.substring(k + 3).trim();

            k = address.indexOf("?");

            if(k < 0) {
                k = address.indexOf("&");
            }

            if(k > -1) {
                address = address.substring(0, k);
            }
        }

        if(protocol == null || address == null) {
            return map;
        }

        if(protocol.equalsIgnoreCase("jdbc:mysql")) {
            String host = null;
            String port = "3306";
            String name = null;
            k = address.indexOf("/");

            if(k > -1) {
                host = address.substring(0, k);
                name = address.substring(k + 1);
            }
            else {
                host = address;
                name = "mysql";
            }

            if(host != null && name != null) {
                k = host.indexOf(":");

                if(k > -1) {
                    port = host.substring(k + 1);
                    host = host.substring(0, k);
                }
                map.put("host", host);
                map.put("port", port);
                map.put("name", name);
            }
        }
        return map;
    }

    /**
     * @param dataType
     * @param dataSize
     * @param decimalDigits
     * @return String
     */
    public static String getColumnDefine(String dataType, int dataSize, int decimalDigits) {
        boolean unsigned = false;
        String typeName = dataType.trim().toUpperCase();
        String result = null;

        if(typeName.endsWith("UNSIGNED")) {
            unsigned = true;
            typeName = typeName.substring(0, typeName.length() - 8).trim();
        }

        if("CHAR".equals(typeName)) {
            result = "CHAR(" + dataSize + ")";
        }
        else if("CHARACTER".equals(typeName)) {
            result = "CHARACTER(" + dataSize + ")";
        }
        else if("VARCHAR".equals(typeName)) {
            result = "VARCHAR(" + dataSize + ")";
        }
        else if("VARCHAR2".equals(typeName)) {
            result = "VARCHAR2(" + dataSize + ")";
        }
        else if("TEXT".equals(typeName)) {
            result = "TEXT";
        }
        else if("LONGTEXT".equals(typeName)) {
            result = "LONGTEXT";
        }
        else if("ENUM".equals(typeName)) {
            result = "ENUM";
        }
        else if("BOOL".equals(typeName) || "BOOLEAN".equals(typeName)) {
            result = "BOOLEAN";
        }
        else if("BIT".equals(typeName)) {
            result = "BIT";
        }
        else if("TINYINT".equals(typeName)) {
            result = "TINYINT(" + dataSize + ")";
        }
        else if("TINYINT UNSIGNED".equals(typeName)) {
            result = "TINYINT(" + dataSize + ") UNSIGNED";
        }
        else if("SMALLINT".equals(typeName) || "SMALLINT UNSIGNED".equals(typeName)) {
            result = typeName + "(" + dataSize + ")";
        }
        else if("MEDIUMINT".equals(typeName) || "MEDIUMINT UNSIGNED".equals(typeName)) {
            result = typeName + "(" + dataSize + ")";
        }
        else if("INT".equals(typeName) || "INT UNSIGNED".equals(typeName)) {
            result = typeName + "(" + dataSize + ")";
        }
        else if("BIGINT".equals(typeName) || "BIGINT UNSIGNED".equals(typeName)) {
            result = typeName + "(" + dataSize + ")";
        }
        else if("INTEGER".equals(typeName)) {
            result = typeName + "(" + dataSize + ")";
        }
        else if("FLOAT".equals(typeName)) {
            result = "FLOAT(" + dataSize + ", " + decimalDigits + ")";
        }
        else if("DOUBLE".equals(typeName) || typeName.startsWith("DOUBLE(")) {
            result = "DOUBLE(" + dataSize + ", " + decimalDigits + ")";
        }
        else if("LONG".equals(typeName)) {
            result = "LONG(" + dataSize + ")";
        }
        else if("NUMBER".equals(typeName)) {
            result = "NUMBER(" + dataSize + ")";
        }
        else if("DATE".equals(typeName)) {
            result = "DATE";
        }
        else if("TIME".equals(typeName)) {
            result = "TIME";
        }
        else if("DATETIME".equals(typeName)) {
            result = "DATETIME";
        }
        else if("TIMESTAMP".equals(typeName) || typeName.startsWith("TIMESTAMP(")) {
            result = "TIMESTAMP";
        }
        else if("YEAR".equals(typeName)) {
            result = "YEAR";
        }
        else if("BLOB".equals(typeName)) {
            result = "BLOB";
        }
        else if("CLOB".equals(typeName)) {
            result = "CLOB";
        }
        else if("RAW".equals(typeName)) {
            result = "RAW(" + dataSize + ", " + decimalDigits + ")";
        }
        else {
            result = typeName + "(" + dataSize + ", " + decimalDigits + ")";
        }
        return (unsigned ? (result + " UNSIGNED") : result);
    }

    /**
     * @param source
     * @return String
     */
    public static String escape(String source) {
        if(source == null) {
            return "";
        }

        char c;
        StringBuilder buffer = new StringBuilder();

        for(int i = 0, length = source.length(); i < length; i++) {
            c = source.charAt(i);

            switch (c) {
                case '\'': {
                    buffer.append("\\'");break;
                }
                case '\r': {
                    buffer.append("\\r");break;
                }
                case '\n': {
                    buffer.append("\\n");break;
                }
                case '\t': {
                    buffer.append("\\t");break;
                }
                case '\b': {
                    buffer.append("\\b");break;
                }
                case '\f': {
                    buffer.append("\\f");break;
                }
                case '\\': {
                    buffer.append("\\\\");break;
                }
                default : {
                    buffer.append(c);break;
                }
            }
        }
        return buffer.toString();
    }

    /**
     * @param statement
     */
    public static void close(Statement statement) {
        if(statement != null) {
            try {
                statement.close();
            }
            catch(SQLException e) {
            }
        }
    }

    /**
     * @param resultSet
     */
    public static void close(ResultSet resultSet) {
        if(resultSet != null) {
            try {
                resultSet.close();
            }
            catch(SQLException e) {
            }
        }
    }

    /**
     * @param connection
     */
    public static void close(Connection connection) {
        if(connection != null) {
            try {
                if(connection.isClosed() == false) {
                    connection.close();
                }
            }
            catch(SQLException e) {
            }
        }
    }
}
